﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Client.Logic.Mobile;
using PPI.Client.Utils;
using PPI.Entities;
using PPI.Entities.Enumerator;
using PPI.Entities.Inspections;
using PPI.Entities.Lookup;
using PPI.Entities.Remedy;
using PPI.Entities.Settings.Mobile;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic.Inspection
{
    public class LInspection
    {
        #region Mobile

        public static EInspection SelectData(string connectionString, Int32 id)
        {
            EInspection returnInspection;
            DatabaseTypes _DatabaseType = LOutbox.GetDatabaseType(connectionString);
            if (_DatabaseType != DatabaseTypes.Project)
                returnInspection = GetMobileById(connectionString, id);
            else
                returnInspection = GetProjectById(connectionString, id);

            return returnInspection;
        }

        private static EInspection GetMobileById(string connectionString, Int32 id)
        {
            DatabaseTypes _DatabaseType = LOutbox.GetDatabaseType(connectionString);
            StringBuilder _sql = new StringBuilder();

            #region Query
            _sql.AppendLine("Select ");
            _sql.AppendLine("   U.UtilityInspectionId, ");
            _sql.AppendLine("   U.InspectionServerID, ");
            _sql.AppendLine("   U.AcquisitionDate, ");
            _sql.AppendLine("   U.MapFk, ");
            _sql.AppendLine("   MA.Name as Map_Name, ");
            _sql.AppendLine("   U.Latitude, ");
            _sql.AppendLine("   U.Longitude, ");
            _sql.AppendLine("   U.TreatmentFk, ");
            _sql.AppendLine("   TRE.LookupValue as Treatment_Name, ");
            _sql.AppendLine("   U.ClassFk, ");
            _sql.AppendLine("   CLA.LookupValue as Class_Name, ");
            _sql.AppendLine("   U.SpecieFk, ");
            _sql.AppendLine("   E.LookupValue as Specie_Name, ");
            _sql.AppendLine("   U.ToptypeFk, ");
            _sql.AppendLine("   T.name as Toptype_Name, ");
            _sql.AppendLine("   U.LengthFk, ");
            _sql.AppendLine("   L.Name as Length_Name, ");
            _sql.AppendLine("   U.Year, ");
            _sql.AppendLine("   U.LVisit, ");
            _sql.AppendLine("   U.Number, ");
            _sql.AppendLine("   U.InspectorFk, ");
            _sql.AppendLine("   I.Name as Inspector_Name, ");
            _sql.AppendLine("   U.CompanyFk, ");
            _sql.AppendLine("   C.Name as Company_Name, ");
            _sql.AppendLine("   U.SectionFk,  ");
            _sql.AppendLine("   U.GuardsQuantity,  ");
            _sql.AppendLine("   U.Billable,  ");
            _sql.AppendLine("   U.PoleTag,  ");
            _sql.AppendLine("   U.GroundWireRepair,  ");
            _sql.AppendLine("   U.LastCompany,  ");
            _sql.AppendLine("   U.VisitTypeFk, ");
            _sql.AppendLine("   VT.LookupText as VisitType_Name  ");
            _sql.AppendLine("FROM tb_UtilityInspections U ");
            _sql.AppendLine("   LEFT JOIN tb_Tops T ON U.ToptypeFk = T.TopId");
            _sql.AppendLine("   LEFT JOIN tb_Companies C ON U.CompanyFk = C.CompanyId");
            _sql.AppendLine("   LEFT JOIN tb_Inspectors I ON U.InspectorFk = I.InspectorId");
            _sql.AppendLine("   LEFT JOIN tb_Lookups TRE ON (U.TreatmentFk = TRE.LookupValue AND TRE.LookupTypeFk = 'Treatments' )");
            _sql.AppendLine("   LEFT JOIN tb_Lookups CLA ON (U.ClassFk = CLA.LookupValue AND CLA.LookupTypeFk = 'Classes' )");
            _sql.AppendLine("   LEFT JOIN tb_Lookups E ON (U.SpecieFk = E.LookupValue AND E.LookupTypeFk = 'Species' )");
            _sql.AppendLine("   LEFT JOIN tb_Lookups VT ON (U.VisitTypeFk = VT.LookupValue AND VT.LookupTypeFk = 'VisitTypes' )");
            _sql.AppendLine("   LEFT JOIN tb_Lengths L ON U.LengthFk = L.LengthId");
            _sql.AppendLine("   LEFT JOIN tb_Maps MA ON U.MapFk = MA.MapId ");
            _sql.AppendLine("Where U.UtilityInspectionId = " + id + " ");
            #endregion Query
            DataTable _dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];
            EMobileSettingsInspection _Settings = (EMobileSettingsInspection)LMobileSettings.LoadMobileSettings(connectionString);

            DataRow _dr = _dt.Rows[0];

            EInspection _inspection = new EInspection();
            _inspection.DatabaseType = _DatabaseType;
            _inspection.ServerID = ConvertHelper.ToInt32(_dr["InspectionServerID"]);
            _inspection.AcquisitionDate = ConvertHelper.ToDateTime(_dr["AcquisitionDate"]);
            _inspection.Class = new EXMissionStructures_PoleClass(ConvertHelper.ToInt32(_dr["ClassFk"]), ConvertHelper.Tostring(_dr["Class_Name"]));
            _inspection.Company = new ECompany(ConvertHelper.ToInt32(_dr["CompanyFk"]), ConvertHelper.Tostring(_dr["Company_Name"]));
            _inspection.Id = ConvertHelper.ToInt32(_dr["UtilityInspectionId"]);
            _inspection.Inspector = new EInspector(ConvertHelper.ToInt32(_dr["InspectorFk"]), ConvertHelper.Tostring(_dr["Inspector_Name"]));
            _inspection.Latitude = ConvertHelper.Todouble(_dr["Latitude"]);
            _inspection.Longitude = ConvertHelper.Todouble(_dr["Longitude"]);
            _inspection.Length = ConvertHelper.ToInt32(_dr["Length_Name"]);
            _inspection.GuardsQuantity = ConvertHelper.ToInt32(_dr["GuardsQuantity"]);
            _inspection.PoleTag = (bool)_dr["PoleTag"];
            _inspection.GroundWireRepair = (bool)_dr["GroundWireRepair"];
            _inspection.Billable = (bool)_dr["Billable"];
            _inspection.LastCompanyName = ConvertHelper.Tostring(_dr["LastCompany"]);
            _inspection.LastVisit = ConvertHelper.ToDateTime(_dr["LVisit"]);
            _inspection.Map = new EMap(ConvertHelper.ToInt32(_dr["MapFk"]), ConvertHelper.Tostring(_dr["Map_Name"]));
            _inspection.Section = new ESection(ConvertHelper.ToInt32(_dr["SectionFk"]));
            _inspection.Number = ConvertHelper.Tostring(_dr["Number"]);
            _inspection.Specie = new EXMissionStructures_PoleSpecies(ConvertHelper.ToInt32(_dr["SpecieFk"]), ConvertHelper.Tostring(_dr["Specie_Name"]));
            _inspection.TopType = new EXMissionStructures_TopType(ConvertHelper.ToInt32(_dr["ToptypeFk"]), ConvertHelper.Tostring(_dr["Toptype_Name"]));
            _inspection.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.ToInt32(_dr["TreatmentFk"]), ConvertHelper.Tostring(_dr["Treatment_Name"]));
            _inspection.Year = ConvertHelper.ToInt32(_dr["Year"]);
            _inspection.Images = LInspectionImage.GetByInspection(connectionString, id);
            _inspection.VendorVisitType = new EVendor_Visit_Type(ConvertHelper.ToInt32(_dr["VisitTypeFk"]), ConvertHelper.Tostring(_dr["VisitType_Name"]));
            _inspection.Contact = _Settings.Contact;
            _inspection.Contact.Vendor = _Settings.Vendor;
            _inspection.Project = _Settings.Project;
            _inspection.Project.Vendor = _Settings.Vendor;
            _inspection.Handheld = _Settings.Handheld;
            
            foreach (EInspectionImage i in _inspection.Images)
                i.Inspection = _inspection;

            return _inspection;
        }

        private static EInspection GetProjectById(string connectionString, Int32 id)
        {
            DatabaseTypes _DatabaseType = LOutbox.GetDatabaseType(connectionString);

            StringBuilder _sql = new StringBuilder();

            #region Query
            _sql.AppendLine("Select ");
            _sql.AppendLine("   U.UtilityInspectionId, ");
            _sql.AppendLine("   U.InspectionServerID, ");
            _sql.AppendLine("   U.AcquisitionDate, ");
            _sql.AppendLine("   U.MapFk, ");
            _sql.AppendLine("   MA.Name as Map_Name, ");
            _sql.AppendLine("   U.Latitude, ");
            _sql.AppendLine("   U.Longitude, ");
            _sql.AppendLine("   U.TreatmentFk, ");
            _sql.AppendLine("   TRE.LookupValue as Treatment_Name, ");
            _sql.AppendLine("   U.ClassFk, ");
            _sql.AppendLine("   CLA.LookupValue as Class_Name, ");
            _sql.AppendLine("   U.SpecieFk, ");
            _sql.AppendLine("   E.LookupValue as Specie_Name, ");
            _sql.AppendLine("   U.ToptypeFk, ");
            _sql.AppendLine("   T.name as Toptype_Name, ");
            _sql.AppendLine("   U.LengthFk, ");
            _sql.AppendLine("   L.Name as Length_Name, ");
            _sql.AppendLine("   U.Year, ");
            _sql.AppendLine("   U.LVisit, ");
            _sql.AppendLine("   U.Number, ");
            _sql.AppendLine("   U.InspectorFk, ");
            _sql.AppendLine("   I.Name as Inspector_Name, ");
            _sql.AppendLine("   U.CompanyFk, ");
            _sql.AppendLine("   U.Vendor_InspVisit_XMissionStructureFk, ");
            _sql.AppendLine("   U.ProjectFk, ");
            _sql.AppendLine("   PR.Name as Project_Name, ");
            _sql.AppendLine("   US.Name as Contact_Name, ");
            _sql.AppendLine("   U.ContactFk, ");
            _sql.AppendLine("   US.VendorFk as Contact_VendorFk, ");
            _sql.AppendLine("   U.Vendor_InspVisit_XMissionStructureFk, ");
            _sql.AppendLine("   V.VendorId, ");
            _sql.AppendLine("   V.Name as Vendor_Name, ");
            _sql.AppendLine("   C.Name as Company_Name, ");
            _sql.AppendLine("   U.SectionFk ");
            _sql.AppendLine("FROM tb_UtilityInspections U ");
            _sql.AppendLine("   LEFT JOIN tb_Tops T ON U.ToptypeFk = T.TopId");
            _sql.AppendLine("   LEFT JOIN tb_Companies C ON U.CompanyFk = C.CompanyId");
            _sql.AppendLine("   LEFT JOIN tb_Inspectors I ON U.InspectorFk = I.InspectorId");
            _sql.AppendLine("   LEFT JOIN tb_Lookups TRE ON (U.TreatmentFk = TRE.LookupValue AND TRE.LookupTypeFk = 'Treatments' )");
            _sql.AppendLine("   LEFT JOIN tb_Lookups CLA ON (U.ClassFk = CLA.LookupValue AND CLA.LookupTypeFk = 'Classes' )");
            _sql.AppendLine("   LEFT JOIN tb_Lookups E ON (U.SpecieFk = E.LookupValue AND E.LookupTypeFk = 'Species' )");
            _sql.AppendLine("   LEFT JOIN tb_Lengths L ON U.LengthFk = L.LengthId");
            _sql.AppendLine("   LEFT JOIN tb_Maps MA ON U.MapFk = MA.MapId ");
            _sql.AppendLine("   LEFT JOIN tb_Users US ON U.ContactFk = US.UserId ");
            _sql.AppendLine("   LEFT JOIN tb_Projects PR ON U.ProjectFk = PR.ProjectId ");
            _sql.AppendLine("   LEFT JOIN tb_Vendor V ON PR.VendorFk = V.VendorId ");
            _sql.AppendLine("Where U.UtilityInspectionId = " + id + " ");
            #endregion Query
            DataTable _dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            DataRow _dr = _dt.Rows[0];

            EInspection _inspection = new EInspection();
            _inspection.DatabaseType = _DatabaseType;
            _inspection.ServerID = ConvertHelper.ToInt32(_dr["InspectionServerID"]);
            _inspection.AcquisitionDate = ConvertHelper.ToDateTime(_dr["AcquisitionDate"]);
            _inspection.Class = new EXMissionStructures_PoleClass(ConvertHelper.ToInt32(_dr["ClassFk"]), ConvertHelper.Tostring(_dr["Class_Name"]));
            _inspection.Company = new ECompany(ConvertHelper.ToInt32(_dr["CompanyFk"]), ConvertHelper.Tostring(_dr["Company_Name"]));
            _inspection.Id = ConvertHelper.ToInt32(_dr["UtilityInspectionId"]);
            _inspection.Inspector = new EInspector(ConvertHelper.ToInt32(_dr["InspectorFk"]), ConvertHelper.Tostring(_dr["Inspector_Name"]));
            _inspection.Latitude = ConvertHelper.Todouble(_dr["Latitude"]);
            _inspection.Longitude = ConvertHelper.Todouble(_dr["Longitude"]);
            _inspection.Length = ConvertHelper.ToInt32(_dr["Length_Name"]);
            _inspection.LastVisit = ConvertHelper.ToDateTime(_dr["LVisit"]);
            _inspection.Map = new EMap(ConvertHelper.ToInt32(_dr["MapFk"]), ConvertHelper.Tostring(_dr["Map_Name"]));
            _inspection.Section = new ESection(ConvertHelper.ToInt32(_dr["SectionFk"]));
            _inspection.Number = ConvertHelper.Tostring(_dr["Number"]);
            _inspection.Specie = new EXMissionStructures_PoleSpecies(ConvertHelper.ToInt32(_dr["SpecieFk"]), ConvertHelper.Tostring(_dr["Specie_Name"]));
            _inspection.TopType = new EXMissionStructures_TopType(ConvertHelper.ToInt32(_dr["ToptypeFk"]), ConvertHelper.Tostring(_dr["Toptype_Name"]));
            _inspection.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.ToInt32(_dr["TreatmentFk"]), ConvertHelper.Tostring(_dr["Treatment_Name"]));
            _inspection.Year = ConvertHelper.ToInt32(_dr["Year"]);
            _inspection.Images = LInspectionImage.GetByInspection(connectionString, id);
            _inspection.Contact = new EVendor_Contact(ConvertHelper.ToInt32(_dr["ContactFk"]), _dr["Contact_Name"].ToString());
            _inspection.Contact.Vendor = new EVendor(ConvertHelper.ToInt32(_dr["VendorId"]), _dr["Vendor_Name"].ToString(), "");
            _inspection.Project = new EVendor_Project(ConvertHelper.ToInt32(_dr["ProjectFk"]), _dr["Project_Name"].ToString());
            _inspection.Project.Vendor = new EVendor(ConvertHelper.ToInt32(_dr["VendorId"]), _dr["Vendor_Name"].ToString(), "");
            _inspection.Structure = new EInspectionStructure(ConvertHelper.ToInt32(_dr["Vendor_InspVisit_XMissionStructureFk"]));

            foreach (EInspectionImage i in _inspection.Images)
                i.Inspection = _inspection;

            return _inspection;
        }

        public static void UpdateUploadedData(EInspection inspection, string connectionString)
        {
            StringBuilder _query = new StringBuilder();

            _query.AppendLine("Update tb_UtilityInspections set InspectionServerID =" + inspection.ServerID + ",UploadedDate = " +
                              DateTime.Now.ToString("yyyy-MM-dd") + " where UtilityInspectionId =" + inspection.Id + " ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);

            _query = new StringBuilder();
            _query.AppendLine("Update tb_Images set InspectionServerID =" + inspection.ServerID + " where UtilityInspectionFk =" + inspection.Id + " ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);


            _query = new StringBuilder();
            _query.AppendLine("update tb_UtilityInspectionResults set InspectionServerID =" + inspection.ServerID +
                              " where UtilityInspectionFk =" + inspection.Id + "   ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
        }


        #region CRUD

        public static void SetStructureMobile(EInspection inspection, EInspectionStructure structure, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            List<SqlParameter> _parameters = new List<SqlParameter>();

            #region SQL

            _sql.AppendLine("Update tb_UtilityInspections SET");
            _sql.AppendLine("Vendor_InspVisit_XMissionStructureFk = @Vendor_InspVisit_XMissionStructureFk");
            _sql.AppendLine("Where UtilityInspectionId  = @UtilityInspectionId");


            #endregion

            #region Parameters

            _parameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspection.Id));

            if (structure.Id != Int32.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureFk", ParameterDirection.Input, false, structure.Id));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureFk", ParameterDirection.Input, true, DBNull.Value));


            #endregion

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(_sql.ToString(), connectionString, _parameters.ToArray());
            }
            catch(Exception ex)
            {
                RegisterError.SaveError(ex);
            }
        }

        public static Int32 SaveMobile(EInspection inspection, string connectionString)
        {
            Int32 returnId = inspection.Id;
            if (inspection.Id != Int32.MinValue)
                UpdateMobile(inspection, connectionString);

            return returnId;
        }

        private static void UpdateMobile(EInspection inspection, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();
            SqlParameter tmpParameter;

            #region Paramters
            listParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspection.Id));
            listParameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, inspection.Latitude));
            listParameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, inspection.Longitude));

            #region Year
            if (inspection.Year != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, inspection.Year);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, inspection.LastVisit));

            #region Number
            if (!string.IsNullOrEmpty(inspection.Number))
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number",255, ParameterDirection.Input, false, inspection.Number);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number",255, ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, inspection.AcquisitionDate));

            #region Treatment
            if (inspection.Treatment.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, inspection.Treatment.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Class
            if (inspection.Class.Id!= Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, inspection.Class.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Specie
            if (inspection.Specie.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, inspection.Specie.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateIntParameter("@ToptypeFk", ParameterDirection.Input, false, inspection.TopType.XMissionStructures_TopTypeId));

            #region Length
            if (inspection.Length != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, inspection.Length);
            else
                tmpParameter = DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, inspection.Inspector.Id));
            listParameters.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, false, inspection.Company.Id));


            #region Map
            if (inspection.Map.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, false, inspection.Map.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Section
            if (inspection.Section.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, inspection.Section.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #endregion

            #region Query

            _Query.AppendLine("Update tb_UtilityInspections SET ");
            _Query.AppendLine("     Latitude = @Latitude,");
            _Query.AppendLine("     Longitude = @Longitude,");
            _Query.AppendLine("     Year = @Year,");
            _Query.AppendLine("     LVisit = @LVisit,");
            _Query.AppendLine("     Number = @Number,");
            _Query.AppendLine("     AcquisitionDate = @AcquisitionDate,");
            _Query.AppendLine("     TreatmentFk = @TreatmentFk,");
            _Query.AppendLine("     ClassFk = @ClassFk,");
            _Query.AppendLine("     SpecieFk = @SpecieFk,");
            _Query.AppendLine("     ToptypeFk = @ToptypeFk,");
            _Query.AppendLine("     LengthFk = @LengthFk,");
            _Query.AppendLine("     InspectorFk = @InspectorFk,");
            _Query.AppendLine("     CompanyFk = @CompanyFk,");
            _Query.AppendLine("     MapFk = @MapFk,");
            _Query.AppendLine("     SectionFk = @SectionFk");
            _Query.AppendLine(" Where UtilityInspectionId = @UtilityInspectionId ");


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameters.ToArray());


        }

        public static void InsertMobileProject(EInspection inspection, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();
            SqlParameter tmpParameter;

            #region Paramters
            listParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspection.Id));
            listParameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, inspection.Latitude));
            listParameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, inspection.Longitude));

            #region Year
            if (inspection.Year != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, inspection.Year);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Project
            if (inspection.Project.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ProjectFk", ParameterDirection.Input, false, inspection.Project.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ProjectFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Contact
            if (inspection.Contact.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ContactFk", ParameterDirection.Input, false, inspection.Contact.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ContactFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Structure
            if (inspection.Structure.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureFk", ParameterDirection.Input, false, inspection.Structure.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, inspection.LastVisit));

            #region Number
            if (!string.IsNullOrEmpty(inspection.Number))
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, false, inspection.Number);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@Number", 255, ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, inspection.AcquisitionDate));

            #region Treatment
            if (inspection.Treatment.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, inspection.Treatment.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Class
            if (inspection.Class.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, inspection.Class.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Specie
            if (inspection.Specie.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, inspection.Specie.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateIntParameter("@ToptypeFk", ParameterDirection.Input, false, inspection.TopType.XMissionStructures_TopTypeId));

            #region Length
            if (inspection.Length != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, inspection.Length);
            else
                tmpParameter = DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            listParameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, inspection.Inspector.Id));
            listParameters.Add(DBHelper.CreateIntParameter("@CompanyFk", ParameterDirection.Input, false, inspection.Company.Id));


            #region Map
            if (inspection.Map.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, false, inspection.Map.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@MapFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region Section
            if (inspection.Section.Id != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, false, inspection.Section.Id);
            else
                tmpParameter = DBHelper.CreateIntParameter("@SectionFk", ParameterDirection.Input, true, DBNull.Value);
            listParameters.Add(tmpParameter);

            #endregion

            #region GuardsQuantity
            if (inspection.GuardsQuantity != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, inspection.GuardsQuantity);
            else
                tmpParameter = DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, true, DBNull.Value);

            listParameters.Add(tmpParameter);

            #endregion

            #region Billable

            tmpParameter = DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, false, inspection.Billable);
            listParameters.Add(tmpParameter);

            #endregion

            #region PoleTag

            tmpParameter = DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, inspection.PoleTag);
            listParameters.Add(tmpParameter);

            #endregion

            #region GroundWireRepair

            tmpParameter = DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, inspection.GroundWireRepair);
            listParameters.Add(tmpParameter);

            #endregion

            #region LInspector
            if (!String.IsNullOrEmpty(inspection.LastCompanyName))
                tmpParameter = DBHelper.CreateNVarCharParameter("@LastCompany", 100, ParameterDirection.Input, false, inspection.LastCompanyName);
            else
                tmpParameter = DBHelper.CreateNVarCharParameter("@LastCompany", 100, ParameterDirection.Input, true, DBNull.Value);

            listParameters.Add(tmpParameter);

            #endregion

            #region Visit Type

            if (inspection.VendorVisitType.Vendor_Visit_TypeId != Int32.MinValue)
                tmpParameter = DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, false, inspection.VendorVisitType.Vendor_Visit_TypeId);
            else
                tmpParameter = DBHelper.CreateIntParameter("@VisitTypeFk", ParameterDirection.Input, true, DBNull.Value);

            listParameters.Add(tmpParameter);

            #endregion


            #endregion

            #region Query

            _Query.AppendLine("Insert into tb_UtilityInspections ");
            _Query.AppendLine("     (");
            _Query.AppendLine("         UtilityInspectionId,");
            _Query.AppendLine("         Latitude,");
            _Query.AppendLine("         Longitude,");
            _Query.AppendLine("         Year,");
            _Query.AppendLine("         LVisit,");
            _Query.AppendLine("         Number,");
            _Query.AppendLine("         AcquisitionDate,");
            _Query.AppendLine("         TreatmentFk,");
            _Query.AppendLine("         ClassFk,");
            _Query.AppendLine("         SpecieFk,");
            _Query.AppendLine("         ToptypeFk,");
            _Query.AppendLine("         LengthFk,");
            _Query.AppendLine("         InspectorFk,");
            _Query.AppendLine("         CompanyFk,");
            _Query.AppendLine("         MapFk,");
            _Query.AppendLine("         ProjectFk,");
            _Query.AppendLine("         ContactFk,");
            _Query.AppendLine("         Vendor_InspVisit_XMissionStructureFk,");
            _Query.AppendLine("         SectionFk,");
            _Query.AppendLine("         GuardsQuantity,");
            _Query.AppendLine("         PoleTag,");
            _Query.AppendLine("         Billable,");
            _Query.AppendLine("         GroundWireRepair,");
            _Query.AppendLine("         LastCompany,");
            _Query.AppendLine("         VisitTypeFk");
            _Query.AppendLine("     )Values(");
            _Query.AppendLine("         @UtilityInspectionId,");
            _Query.AppendLine("         @Latitude,");
            _Query.AppendLine("         @Longitude,");
            _Query.AppendLine("         @Year,");
            _Query.AppendLine("         @LVisit,");
            _Query.AppendLine("         @Number,");
            _Query.AppendLine("         @AcquisitionDate,");
            _Query.AppendLine("         @TreatmentFk,");
            _Query.AppendLine("         @ClassFk,");
            _Query.AppendLine("         @SpecieFk,");
            _Query.AppendLine("         @ToptypeFk,");
            _Query.AppendLine("         @LengthFk,");
            _Query.AppendLine("         @InspectorFk,");
            _Query.AppendLine("         @CompanyFk,");
            _Query.AppendLine("         @MapFk,");
            _Query.AppendLine("         @ProjectFk,");
            _Query.AppendLine("         @ContactFk,");
            _Query.AppendLine("         @Vendor_InspVisit_XMissionStructureFk,");
            _Query.AppendLine("         @SectionFk,");
            _Query.AppendLine("         @GuardsQuantity,");
            _Query.AppendLine("         @PoleTag,");
            _Query.AppendLine("         @Billable,");
            _Query.AppendLine("         @GroundWireRepair,");
            _Query.AppendLine("         @LastCompany,");
            _Query.AppendLine("         @VisitTypeFk");
            _Query.AppendLine("     )");
            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameters.ToArray());


        }

        public static void DeleteMobile(EInspection inspection, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();
            SqlParameter tmpParameter;

            #region Paramters
            listParameters.Add(DBHelper.CreateIntParameter("@UtilityInspectionId", ParameterDirection.Input, false, inspection.Id));


            #endregion

            #region Query

            _Query.AppendLine("Delete tb_UtilityInspections ");
            _Query.AppendLine(" Where UtilityInspectionId = @UtilityInspectionId ");


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameters.ToArray());


        }

        #endregion


        #endregion

        #region CRUD

        public static Int32 Save(EInspection inspection)
        {
            return
                WebConsumer.Inspection.WInspection.Save(inspection.ServerID, inspection.Latitude, inspection.Longitude, inspection.Year, inspection.LastVisit, inspection.Number,
                                                        inspection.AcquisitionDate, inspection.Treatment.Id, inspection.Class.Id, inspection.Specie.Id,inspection.TopType.XMissionStructures_TopTypeId,
                                                        inspection.Length,inspection.Inspector.Id,inspection.Company.Id,inspection.Map.Id,inspection.Project.Id,inspection.Contact.Id,inspection.Structure.Id,inspection.Section.Id, inspection.GuardsQuantity, 
                                                        inspection.PoleTag, inspection.Billable, inspection.GroundWireRepair,
                                                        inspection.LastCompanyName, inspection.VendorVisitType.Vendor_Visit_TypeId);
        }

        #endregion

        public static EInspection ParseDatatableInspection(DataTable dtInspection)
        {
            DataRow _dr = dtInspection.Rows[0];
            EInspection _inspection = new EInspection();
            _inspection.Structure = new EInspectionStructure(ConvertHelper.ToInt32(_dr["Vendor_InspVisit_XMissionStructureFk"]));
            _inspection.Id = ConvertHelper.ToInt32(_dr["Vendor_InspVisit_XMissionStructure_PoleId"]);
            _inspection.ServerID = ConvertHelper.ToInt32(_dr["Vendor_InspVisit_XMissionStructure_PoleId"]);
            _inspection.AcquisitionDate = ConvertHelper.ToDateTime(_dr["AcquisitionDate"]);
            _inspection.Class = new EXMissionStructures_PoleClass(ConvertHelper.ToInt32(_dr["ClassFK"]), ConvertHelper.Tostring(_dr["Class_Name"]));
            _inspection.Company = new ECompany(ConvertHelper.ToInt32(_dr["CompanyFk"]));
            _inspection.Inspector = new EInspector(ConvertHelper.ToInt32(_dr["InspectorFk"]));
            _inspection.Latitude = ConvertHelper.Todouble(_dr["Latitude"]);
            _inspection.Longitude = ConvertHelper.Todouble(_dr["Longitude"]);
            _inspection.Length = ConvertHelper.ToInt32(_dr["Length"]);
            _inspection.LastVisit = ConvertHelper.ToDateTime(_dr["LVisit"]);
            _inspection.Map = new EMap(ConvertHelper.ToInt32(_dr["MapFk"]));
            _inspection.Section = new ESection(ConvertHelper.ToInt32(_dr["SectionFk"]));
            _inspection.Number = ConvertHelper.Tostring(_dr["Number"]);
            _inspection.Specie = new EXMissionStructures_PoleSpecies(ConvertHelper.ToInt32(_dr["SpecieFk"]));
            _inspection.TopType = new EXMissionStructures_TopType(ConvertHelper.ToInt32(_dr["ToptypeFk"]));
            _inspection.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.ToInt32(_dr["TreatmentFk"]));
            _inspection.Year = ConvertHelper.ToInt32(_dr["Year"]);
            _inspection.Images = LInspectionImage.GetByInspection(_inspection.Id);
            _inspection.GuardsQuantity = ConvertHelper.ToInt32(_dr["GuardsQuantity"]);
            _inspection.Billable = (bool)_dr["Billable"];
            _inspection.PoleTag = (bool)_dr["PoleTag"];
            _inspection.GroundWireRepair = (bool)_dr["GroundWireRepair"];
            _inspection.LastCompanyName = ConvertHelper.Tostring(_dr["LInspector"]);
            _inspection.VendorVisitType = new EVendor_Visit_Type(ConvertHelper.ToInt32(_dr["Vendor_Visit_TypeFk"]), ConvertHelper.Tostring(_dr["VendorVisitType_Name"]));
            _inspection.Project = new EVendor_Project(ConvertHelper.ToInt32(_dr["Vendor_ProjectFk"]));
            _inspection.Contact = new EVendor_Contact(ConvertHelper.ToInt32(_dr["Vendor_ContactFk"]));

            return _inspection;
        }

        public static EInspection GetInspectionById(int inspectionId)
        {
            DataTable InspectionDatatable = WebConsumer.Inspection.WInspection.GetInspectionById(inspectionId).Tables[0];
            return ParseDatatableInspection(InspectionDatatable);
        }

        public static void SetVisit(EInspection inspection, Int32 visitId)
        {
            WebConsumer.Inspection.WInspection.SetVisit(inspection.Id,visitId);
        }

        public static Int32 CreateVisit(EInspection inspection, List<EInspection> inspectionList)
        {
            List<Int32> inspectionListId = new List<int>();
            foreach (EInspection i in inspectionList)
            {
                inspectionListId.Add(i.Id);
            }
            return WebConsumer.Inspection.WInspection.NewVisit(inspection.Id, inspectionListId);
        }

      

        #region Remedy

        public static Int32 CreateRemedy(Int32 inspectionPoleFk, DateTime discoveredDate, Int32 inspectionResultTypeFk)
        {
            return WebConsumer.Inspection.WInspection.CreateRemedy(inspectionPoleFk, discoveredDate, inspectionResultTypeFk);
        }

        public static void UpdateRemedy(ERemedy remedy)
        {
            WebConsumer.Inspection.WInspection.UpdateRemedy(remedy.Id, remedy.Status.Id, remedy.Assigned, remedy.LastAction, remedy.ResolvedDate, remedy.Notes);
        }

        public static List<EInspection> SelectAllInspectionRemedies()
        {
            List<EInspection> inspectionList = new List<EInspection>();
            DataSet ds = WebConsumer.Utility.WRemedy.SelectAll();
            if (ds.Tables.Count > 0)
            {
                DataTable dtInspectionRemedies = ds.Tables[0];
                inspectionList = ParseDatatableRemediesInspection(dtInspectionRemedies);
            }
            return inspectionList;
        }

        private static List<EInspection> ParseDatatableRemediesInspection(DataTable dt)
        {
            List<EInspection> listInspections = new List<EInspection>();
            EInspection tmpInspection;
            ERemedy tmpRemedy;

            foreach (DataRow dr in dt.Rows)
            {
                tmpRemedy = new ERemedy();
                tmpRemedy.Id = ConvertHelper.ToInt32(dr["Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId"]);
                tmpRemedy.Assigned = dr["RemedyAssigned"].ToString();
                tmpRemedy.LastAction = ConvertHelper.ToDateTime(dr["RemedyLastAction"]);
                tmpRemedy.ResolvedDate = ConvertHelper.ToDateTime(dr["RemedyResolvedDate"]);
                tmpRemedy.Notes = dr["RemedyNotes"].ToString();
                tmpRemedy.Status = new ERemedyStatus(ConvertHelper.ToInt32(dr["RemedyStatusFK"]), dr["RemedyStatus_Name"].ToString());

                tmpInspection = new EInspection();
                tmpInspection.Structure = new EInspectionStructure(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XMissionStructureFk"]));
                tmpInspection.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XMissionStructure_PoleId"]);
                tmpInspection.ServerID = tmpInspection.Id;
                tmpInspection.AcquisitionDate = ConvertHelper.ToDateTime(dr["AcquisitionDate"]);
                tmpInspection.Class = new EXMissionStructures_PoleClass(ConvertHelper.ToInt32(dr["ClassFK"]), ConvertHelper.Tostring(dr["Class_Name"]));
                tmpInspection.Company = new ECompany(ConvertHelper.ToInt32(dr["CompanyFk"]));
                tmpInspection.Inspector = new EInspector(ConvertHelper.ToInt32(dr["InspectorFk"]));
                tmpInspection.Latitude = ConvertHelper.Todouble(dr["Latitude"]);
                tmpInspection.Longitude = ConvertHelper.Todouble(dr["Longitude"]);
                tmpInspection.Length = ConvertHelper.ToInt32(dr["Length"]);
                tmpInspection.LastVisit = ConvertHelper.ToDateTime(dr["LVisit"]);
                tmpInspection.Map = new EMap(ConvertHelper.ToInt32(dr["MapFk"]));
                tmpInspection.Map.Name = dr["Map_Name"].ToString();
                tmpInspection.Section = new ESection(ConvertHelper.ToInt32(dr["SectionFk"]));
                tmpInspection.Number = ConvertHelper.Tostring(dr["Number"]);
                tmpInspection.Specie = new EXMissionStructures_PoleSpecies(ConvertHelper.ToInt32(dr["SpecieFk"]), dr["Specie_Name"].ToString());
                tmpInspection.TopType = new EXMissionStructures_TopType(ConvertHelper.ToInt32(dr["TopTypeFk"]), dr["Toptype_Name"].ToString());
                tmpInspection.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.ToInt32(dr["TreatmentFk"]),dr["Treatment_Name"].ToString());
                tmpInspection.Year = ConvertHelper.ToInt32(dr["Year"]);
                tmpInspection.Images = LInspectionImage.GetByInspection(tmpInspection.Id);
                tmpInspection.Project = new EVendor_Project(ConvertHelper.ToInt32(dr["Vendor_ProjectFK"]), dr["VendorProject_Name"].ToString());
                tmpInspection.Result.Comments = dr["Comments"].ToString();
                tmpInspection.Result.IsElectrical = ConvertHelper.Tobool(dr["bElectrical"]);
                tmpInspection.Result.InspectionType = new EInspectionTypes(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_Inspection_TypeFk"]), dr["Result_InspectionType_Name"].ToString());
                tmpInspection.Result.Resultype = new EResultType(ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultTypeFk"]), dr["Result_ResultType_Name"].ToString());
                tmpInspection.Result.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultId"]);
                tmpInspection.Result.Notes = LNote.GetByResultId(tmpInspection.Result.Id);
                tmpInspection.UtilityAsset.Id = ConvertHelper.ToInt32(dr["Structure_AssetId"]);

                tmpRemedy.Inspection = tmpInspection;
                tmpInspection.CrossRemedies.Add(tmpRemedy);
                
                listInspections.Add(tmpInspection);
            }
            return listInspections;
   

        }

        #endregion
    }
}
